package com.lingying.caiwudailikehudaun.mapper.piaoJuLieBiao;

import com.lingying.caiwudailikehudaun.domain.zuoZhangPiaoJu.FaPiao;
import com.lingying.caiwudailikehudaun.domain.zuoZhangPiaoJu.FilePiaoJuJiaoJieBean;
import com.lingying.caiwudailikehudaun.domain.zuoZhangPiaoJu.PiaoJuJieBean;
import com.lingying.caiwudailikehudaun.domain.zuoZhangShenBaoBean.ZuoZhangShenBaoBean;
import com.lingying.caiwudailikehudaun.dto.PiaoJuJiaoJieDTO;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

@Component
@Mapper
public interface PiaoJuLieBiaoMapper {


    ////////////////////管理员查看所有的票据列表/////////////////////////

    /**
     * 最高管路员查询所有人员的票据交接列表
     * 尹浩20210221
     * @param jiGouId
     * @param zhaiYao
     * @param companyName
     * @param jiaoJieType
     * @param sendUser
     * @param acceptUser
     * @param jiaoJieFangShi
     * @param tianXieUserId
     * @param page
     * @param limit
     * @return
     */
    @Select({"<script>" +
            "select" +
            " p.piaoJuJiaoJieId , (select z.companyName from ZuoZhangShenBao z where z.daiLiShangCid = p.daiLiShangCid) companyName, " +
            "p.jiaojieType , p.sendUser, p.daiLiShangCid , " +
            "p.acceptUser, p.tianXieUserName ," +
            "p.jiaoJieFangShi , p.otherFangShi , p.yongTu , p.dianFuJinE ,  " +
            "(select u.userName from user u where u.userId = p.tianXieUserId) tianXieUser , p.createTime " +
            "from piaoJuJiaoJie p  left join filePiaoJuJiaoJie f on p.piaoJuJiaoJieId = f.filePiaoJuJiaoJieId " +
            "WHERE 1=1 and p.jiGouId = #{jiGouId}" +
            "<if test=\" companyName != null and companyName != ''\">" +
            " AND p.daiLiShangCompanyName LIKE CONCAT ('%',#{companyName},'%')" +
            "</if>" +
            "<if test=\" zhaiYao != null and zhaiYao != ''\">" +
            " AND f.zhaiYao like CONCAT('%',#{zhaiYao},'%')" +
            "</if>" +
            "<if test=\" jiaoJieType != null and jiaoJieType != ''\">" +
            " and p.jiaoJieType = #{jiaoJieType}  " +
            "</if>" +
            "<if test=\" sendUser != null and sendUser != ''\">" +
            " and p.sendUser = #{sendUser} " +
            "</if>" +
            "<if test=\" acceptUser != null and acceptUser != ''\">" +
            " and p.acceptUser = #{acceptUser} " +
            "</if>" +
            "<if test=\" jiaoJieFangShi != null and jiaoJieFangShi != ''\">" +
            " and p.jiaoJieFangShi = #{jiaoJieFangShi}  " +
            "</if>" +
            "<if test=\" tianXieUserId != null and tianXieUserId != ''\">" +
            " and p.tianXieUserId = #{tianXieUserId} " +
            "</if>" +
            "order by p.createTime desc limit  #{page} , #{limit}" +
            "</script>"})
    List<PiaoJuJiaoJieDTO> queryPiaoJuJiaoJieList(
            Integer jiGouId,
            String zhaiYao, String companyName, String jiaoJieType,
            String sendUser, String acceptUser,
            String jiaoJieFangShi, Integer tianXieUserId
            , Integer page, Integer limit);

    /**
     * 最高管路员查询所有人员的票据交接列表总数
     * 尹浩20210221
     * @param jiGouId
     * @param zhaiYao
     * @param companyName
     * @param jiaoJieType
     * @param sendUser
     * @param acceptUser
     * @param jiaoJieFangShi
     * @param tianXieUserId
     * @return
     */
    @Select({"<script>" +
            "select count(1)" +
            " from piaoJuJiaoJie p left join filePiaoJuJiaoJie f on p.piaoJuJiaoJieId = f.filePiaoJuJiaoJieId " +
            "WHERE 1=1 and p.jiGouId = #{jiGouId}" +
            "<if test=\" companyName != null and companyName != ''\">" +
            " AND p.daiLiShangCompanyName LIKE CONCAT ('%',#{companyName},'%')" +
            "</if>" +
            "<if test=\" zhaiYao != null and zhaiYao != ''\">" +
            " AND f.zhaiYao like CONCAT('%',#{zhaiYao},'%')" +
            "</if>" +
            "<if test=\" jiaoJieType != null and jiaoJieType != ''\">" +
            " and p.jiaoJieType = #{jiaoJieType}  " +
            "</if>" +
            "<if test=\" sendUser != null and sendUser != ''\">" +
            " and p.sendUser = #{sendUser} " +
            "</if>" +
            "<if test=\" acceptUser != null and acceptUser != ''\">" +
            " and p.acceptUser = #{acceptUser} " +
            "</if>" +
            "<if test=\" jiaoJieFangShi != null and jiaoJieFangShi != ''\">" +
            " and p.jiaoJieFangShi = #{jiaoJieFangShi}  " +
            "</if>" +
            "<if test=\" tianXieUserId != null and tianXieUserId != ''\">" +
            " and p.tianXieUserId = #{tianXieUserId} " +
            "</if>" +
            "</script>"})
    Integer queryPiaoJuJiaoJieCount(
            Integer jiGouId,
            String zhaiYao,String companyName, String jiaoJieType,
            String sendUser, String acceptUser,
            String jiaoJieFangShi, Integer tianXieUserId);





    /**
     * 查询出正在服务的公司
     * @return
     */
    @Select({"<script>" +
            "select z.daiLiShangCid , z.companyName\n" +
            "from ZuoZhangShenBao z where z.jiGouId = #{jiGouId} and fuWuZhuangTai=0 and xianShiZhuangTai=0" +
            "<if test=\" daiLiShangCid != null and daiLiShangCid != ''\">" +
            "and z.daiLiShangCid = #{daiLiShangCid}" +
            "</if>" +
            "</script>"})
    List<ZuoZhangShenBaoBean> chengjiaoList(Integer daiLiShangCid, Integer jiGouId);


    /**
     * 添加票据交接
     * @param piaoJuJieBean
     * @return
     */
    @Insert("insert  into piaoJuJiaoJie \n" +
            "(daiLiShangCompanyName,suoShuQi,jiGouId,jiGouName,daiLiShangCid,jiaoJieType,sendUser,acceptUser,jiaoJieFangShi,otherFangShi," +
            "yongTu,dianFuJinE,tianXieUserId,tianXieUserName,jiaoJieMiYao)\n" +
            "VALUES\n" +
            "(#{daiLiShangCompanyName},#{suoShuQi},#{jiGouId},#{jiGouName},#{daiLiShangCid},#{jiaoJieType},#{sendUser},#{acceptUser}," +
            "#{jiaoJieFangShi},#{otherFangShi},#{yongTu},#{dianFuJinE},#{tianXieUserId},#{tianXieUserName},#{jiaoJieMiYao})")
    @Options(useGeneratedKeys = true , keyProperty = "piaoJuJiaoJieId")
    Integer addPiaoJuJiaoJie(PiaoJuJieBean piaoJuJieBean);


    /**
     * 删除票据  实际上是修改票据的状态 status 0.删除  1.不删除
     * @param fileId
     * @return
     */
    @Update("update faPiao set `status` = 0 where faPiaoId = #{fileId}")
    Integer delPiaoJu(Integer fileId);

    /**
     * 查询发票
     * and createTime > DATE_SUB(NOW(), INTERVAL 60 MINUTE);
     * @param companyName
     * @return
     */
    @Select("SELECT * from faPiao WHERE  SellerName=#{companyName} or PurchaserName=#{companyName} ")
    List<Map> queryFaPiaoByCompanyName(String companyName);

    /**
     * 获取上传文件id
     * @param faPiaoId
     * @return
     */
    @Select("select uploadFileId FROM fileWuPinJiaoJie WHERE piaoJuId=#{faPiaoId}")
    Integer getUploadFileId(Integer faPiaoId);

    /**
     * 查询增值税里边的文件 MD5值
     * @return
     */
    @Select("select f.fileMd5 from faPiao f ")
    List<String> fileMd5List();

    /**
     * 根据票据交接Id查询 交接里边的发票
     * @param piaoJuJiaoJieId
     * @return
     */
    @Select("select fp.zhangTaoId,fp.xiaoHuoFangName,fp.shouZhiMingChengName,fp.xiaoHuoFangName,fp.gouHuoFangName,fp.faPiaoid,fp.zhaiYao,fp.luZhangBiaoShi,\n" +
            "           (select f.ossPath from faPiao f where f.faPiaoId  in(fp.faPiaoid)) filePath,\n" +
            //"           (select p.daiLiShangCid from piaoJuJiaoJie p where p.piaoJuJiaoJieId = #{piaoJuJiaoJieId}) companyGuiMo,\n" +
            "           (select f.InvoiceTypeOrg from faPiao f where f.faPiaoId  in(fp.faPiaoid)) piaoZhong,\n" +
            "           (select f.InvoiceType from faPiao f where f.faPiaoId  in(fp.faPiaoid)) InvoiceType,\n" +
            "           (select f.TotalAmount from faPiao f where f.faPiaoId  in(fp.faPiaoid)) huoWuZongJi,\n" +
            "           (select f.TotalTax from faPiao f where f.faPiaoId  in(fp.faPiaoid)) shuiJinZongJi,\n" +
            "           (select f.AmountInFiguers from faPiao f where f.faPiaoId  in(fp.faPiaoid)) heji,\n" +
            "           (select f.InvoiceDate from faPiao f where f.faPiaoId  in(fp.faPiaoid)) kaiPiaoDate,\n" +
            "           fp.shouZhiLeiXing,fp.shouZhiMingCheng,fp.fuKuanFangShi,fp.pingZhengLuRuDate,fp.yongTu\n" +
            "           from filePiaoJuJiaoJie fp\n" +
            "           where fp.piaoJuJiaoJieId =#{piaoJuJiaoJieId}")
    List<Map<String ,Object>> queryPiaoJuList(Integer piaoJuJiaoJieId);



    /**
     * 管理员查询 票据列表的模糊查询 查询总数据
     * 桑满 20210603
     * @param AmountInFiguers
     * @param suoShuQi
     * @param gouHuoFangName
     * @param xiaoHuoFangName
     * @param page
     * @param limit
     * @return
     */
    @Select({"<script>" +
            " select f.faPiaoId,f.DateChuZuChe,fp.shouZhiMingChengName,fp.ruZhangXinXiQueRenZhangTai,fp.filePiaoJuJiaoJieId," +
            "(select z.gongSiZhuTi from shenBaoJiBenSheZhi z where fp.daiLiShangCid = z.daiLiShangCid ) AS gongSiZhuTi, " +
            "fp.daiLiShangCid," +
            "fp.zhuanPiaoRenZhengZhuangTai," +
            "fp.daiLiShangCompanyName,f.InvoiceType,fp.jiaShuiHeJiJinE,fp.zhaiYao,fp.suoShuQi,fp.gouHuoFangName,fp.xiaoHuoFangName,fp.luZhangBiaoShi,fp.v_id,fp.vg,fp.v_num,fp.period,\n" +
            "fp.shouZhiLeiXing,fp.shouZhiMingCheng,fp.fuKuanFangShi,fp.yongTu,f.InvoiceDate,f.createTime,f.addUserName,f.addUserId,\n" +
            "fp.pingZhengLuRuDate,fp.buHanShuiJinE,fp.jiaShuiHeJiJinE,fp.shuiE,fp.piaoZhongId,fp.zongJinE,fp.zhangShu,fp.luZhangBiaoShi,fp.piaoZhongName,fp.ticketRatesHC\n" +
            "from filePiaoJuJiaoJie fp , faPiao f \n" +
            "where fp.faPiaoId = f.faPiaoId and fp.daiLiShangCid = #{daiLiShangCid}\n" +
            "<if test=\" piaoZhongId != null \">" +
            "and fp.piaoZhongId = #{piaoZhongId}\n" +
            "</if>" +
            "<if test=\" AmountInFiguers != null and AmountInFiguers != ''\">" +
            "and f.AmountInFiguers like concat('%', #{AmountInFiguers} ,'%')" +
            "</if>" +
            "<if test=\" suoShuQi != null and suoShuQi != ''\">" +
            "and fp.suoShuQi = #{suoShuQi}" +
            "</if>" +
            "<if test=\" gouHuoFangName != null and gouHuoFangName != ''\">" +
            "and fp.gouHuoFangName = #{gouHuoFangName}\n" +
            "</if>" +
            "<if test=\" xiaoHuoFangName != null and xiaoHuoFangName != ''\">" +
            "and fp.xiaoHuoFangName = #{xiaoHuoFangName}\n" +
            "</if>" +
            " <if test=\"startSuoShuQi != '' and startSuoShuQi != null and endSuoShuQi != '' and endSuoShuQi != null\">\n" +
            "            AND suoShuQi &gt;= #{startSuoShuQi} and suoShuQi &lt;= #{endSuoShuQi}\n" +
            "        </if>" +
            "<if test=\" xianShiZhuangTai != null and xianShiZhuangTai != ''\">" +
            "and fp.xianShiZhuangTai = #{xianShiZhuangTai}" +
            "</if>" +
            " order by f.createTime desc limit #{page},#{limit}" +
            "</script>"})
    List<Map<String,Object>> piaoJuList(Integer daiLiShangCid,Integer piaoZhongId,String AmountInFiguers,String suoShuQi,
                                        String gouHuoFangName,String xiaoHuoFangName,
                                        String startSuoShuQi,String endSuoShuQi,Integer xianShiZhuangTai,
                                        Integer page,Integer limit);

    /**
     * 管理员查询票据列表模糊查询 查询总条数
     * 桑满 20210603
     * @param AmountInFiguers
     * @param suoShuQi
     * @param gouHuoFangName
     * @param xiaoHuoFangName
     * @return
     */
    @Select({"<script>" +
            " select count(1) \n" +
            "from filePiaoJuJiaoJie fp , faPiao f \n" +
            "where fp.faPiaoId = f.faPiaoId and fp.daiLiShangCid = #{daiLiShangCid}\n" +
            "<if test=\" piaoZhongId != null \">" +
            "and fp.piaoZhongId = #{piaoZhongId}\n" +
            "</if>" +
            "<if test=\" AmountInFiguers != null and AmountInFiguers != ''\">" +
            "and f.AmountInFiguers like concat('%', #{AmountInFiguers} ,'%')" +
            "</if>" +
            "<if test=\" suoShuQi != null and suoShuQi != ''\">" +
            "and fp.suoShuQi = #{suoShuQi}" +
            "</if>" +
            "<if test=\" gouHuoFangName != null and gouHuoFangName != ''\">" +
            "and fp.gouHuoFangName = #{gouHuoFangName}\n" +
            "</if>" +
            "<if test=\" xiaoHuoFangName != null and xiaoHuoFangName != ''\">" +
            "and fp.xiaoHuoFangName = #{xiaoHuoFangName}\n" +
            "</if>" +
            " <if test=\"startSuoShuQi != '' and startSuoShuQi != null and endSuoShuQi != '' and endSuoShuQi != null\">\n" +
            "            AND suoShuQi &gt;= #{startSuoShuQi} and suoShuQi &lt;= #{endSuoShuQi}\n" +
            "        </if>" +
            "<if test=\" xianShiZhuangTai != null and xianShiZhuangTai != ''\">" +
            "and fp.xianShiZhuangTai = #{xianShiZhuangTai}" +
            "</if>" +
            " order by f.createTime desc " +
            "</script>"})
    Integer piaoJuListCount(Integer daiLiShangCid,Integer piaoZhongId,String AmountInFiguers,String suoShuQi,
                            String gouHuoFangName,String xiaoHuoFangName,
                            String startSuoShuQi,String endSuoShuQi,Integer xianShiZhuangTai);



    /**
     * 查询发票交接的销货方公司
     * 桑满20210603
     * @return
     */
    @Select("select DISTINCT xiaoHuoFangName from filePiaoJuJiaoJie where daiLiShangCid = #{daiLiShangCid} and xiaoHuoFangName is not null")
    List<FilePiaoJuJiaoJieBean> queryXiaoHuoFangList(Integer daiLiShangCid);

    /**
     * 查询发票交接的购货方公司
     * 桑满20210603
     * @return
     */
    @Select("select DISTINCT gouHuoFangName from filePiaoJuJiaoJie where daiLiShangCid = #{daiLiShangCid} and xiaoHuoFangName is not null")
    List<FilePiaoJuJiaoJieBean> queryGouHuoFangList(Integer daiLiShangCid);

    /**
     * 根据机构id查询出识别发票的票种 去重
     * 桑满20210603
     * @return
     */
    @Select("select DISTINCT moBanType from faPiao where daiLiShangCid = #{daiLiShangCid}")
    List<FaPiao> queryPiaoZhong(Integer daiLiShangCid);

    /**
     * 根据发票id查询 发票信息 发票路径，销购货方，税额，
     * 不含税金额，含税金额，开票日期 用途
     * @param faPiaoid
     * @return
     */
    @Select("select fp.zhangTaoId,fp.shouZhiMingChengName,fp.shouZhiLeiXing,fp.xiaoHuoFangName,fp.gouHuoFangName,fp.faPiaoid,fp.zhaiYao,fp.luZhangBiaoShi,fp.jiaShuiHeJiJinE,fp.daiLiShangCompanyName,\n" +
            "                  (select f.ossPath from faPiao f where f.faPiaoId  in(fp.faPiaoid)) filePath,\n" +
            "                  (select f.InvoiceTypeOrg from faPiao f where f.faPiaoId  in(fp.faPiaoid)) InvoiceTypeOrg,\n" +
            "                  (select f.InvoiceType from faPiao f where f.faPiaoId  in(fp.faPiaoid)) InvoiceType,\n" +
            "                  (select f.DateChuZuChe from faPiao f where f.faPiaoId  in(fp.faPiaoid)) DateChuZuChe,\n" +
            "                  (select f.dateXingChegnDan from faPiao f where f.faPiaoId  in(fp.faPiaoid)) dateXingChegnDan,\n" +
            "                  (select f.shouPiaoFangXingChegnDan from faPiao f where f.faPiaoId  in(fp.faPiaoid)) shouPiaoFangXingChegnDan,\n" +
            "                  (select f.zongJiaXingChengDan from faPiao f where f.faPiaoId  in(fp.faPiaoid)) zongJiaXingChengDan,\n" +
            "                  (select f.TotalAmount from faPiao f where f.faPiaoId  in(fp.faPiaoid)) huoWuZongJi,\n" +
            "                  (select f.TotalTax from faPiao f where f.faPiaoId  in(fp.faPiaoid)) shuiJinZongJi,\n" +
            "                  (select f.AmountInFiguers from faPiao f where f.faPiaoId  in(fp.faPiaoid)) heji,\n" +
            "                  (select f.result from faPiao f where f.faPiaoId  in(fp.faPiaoid)) result,\n" +
            "                  (select f.InvoiceDate from faPiao f where f.faPiaoId  in(fp.faPiaoid)) kaiPiaoDate,\n" +
            "                  fp.shouZhiLeiXing,fp.shouZhiMingCheng,fp.fuKuanFangShi,fp.pingZhengLuRuDate,fp.zhuanPiaoRenZhengZhuangTai,fp.zhuanPiaoRenZhengName,fp.yongTu,fp.ticketRatesHC\n" +
            "                  from filePiaoJuJiaoJie fp where fp.daiLiShangCid = #{daiLiShangCid} and fp.faPiaoid = #{faPiaoid}")
    Map<String,Object> faPiaoMap(Integer daiLiShangCid,Integer faPiaoid);

}
